﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;

namespace Business
{
    public class ProductLDBiz
    {
        public int SaveItem(ProductLD item)
        {
            var dbContext = new Context();

            dbContext.ProductLDs.Add(item);
            dbContext.SaveChanges();

            return item.Id;
        }

        public void UpdateItem(ProductLD item)
        {
            var dbContext = new Context();
            dbContext.Entry(item).State = EntityState.Modified;
            dbContext.SaveChanges();
        }

        public ProductLD LoadItem(int id)
        {
            var dbContext = new Context();
            return (from item in dbContext.ProductLDs
                    where item.Id == id
                    select item).First();
        }

        public void RemoveItem(List<ProductLD> items)
        {
            var dbContext = new Context();

            string ids = string.Empty;

            foreach (var item in items)
            {
                ids += item.Id + ",";
            }

            if (ids.EndsWith(","))
            {
                ids = ids.Substring(0, ids.Length-1);

                string deleteCommand = string.Format(Constants.DeleteCommand, "ProductLD", ids);
                dbContext.Database.ExecuteSqlCommand(deleteCommand);
            }            
        }

        public List<ProductLD> LoadItems()
        {
            var dbContext = new Context();
            return (from item in dbContext.ProductLDs
                    orderby item.Name
                    select item).ToList();
        }

        public List<SearchProductLD> SearchProduct(string type, string name, string code)
        {
            var typeParam = new SqlParameter { ParameterName = "@Type", Value = DBNull.Value, DbType = DbType.String };
            var nameParam = new SqlParameter { ParameterName = "@Name", Value = DBNull.Value, DbType = DbType.String };
            var codeParam = new SqlParameter { ParameterName = "@Code", Value = DBNull.Value, DbType = DbType.String };

            if (!string.IsNullOrEmpty(type))
            {
                typeParam.Value = type;
            }

            if (!string.IsNullOrEmpty(name))
            {
                nameParam.Value = name;
            }

            if (!string.IsNullOrEmpty(code))
            {
                codeParam.Value = code;
            }

            var dbContext = new Context();
            var data = dbContext.Database.SqlQuery<SearchProductLD>("SearchProductLD @Type, @Name, @Code"
                , typeParam, nameParam, codeParam).ToList();

            return data;
        }
    }
}
